package com.mmy.dao.oa;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.oa.MaterialBase;

public interface MaterialBaseRepository extends JpaRepository<MaterialBase, Integer> {

	/*
	 * 获取最大值id
	 */
	@Query(value = "select MAX(iMaterialID) from tMaterialBase", nativeQuery = true)
	Object getMaxMaterialID();

	/*
	 * 根据name查找
	 */
	@Query(value = "select *  from tMaterialBase where cMaterialName like %:strMaterialName%", nativeQuery = true)
	List<MaterialBase> findByMaterialName(@Param("strMaterialName") String strMaterialName);

	/*
	 * 根据id删除
	 */
	@Transactional
	@Modifying
	@Query(value = "delete from tMaterialBase where tMaterialBase.iMaterialID in (:lstMaterialID)", nativeQuery = true)
	void delMaterialBase(@Param("lstMaterialID") List<Integer> lstMaterialID);

	/*
	 * 根据id查找
	 */
	@Query(value = "select *  from tMaterialBase where iMaterialID = :iMaterialID", nativeQuery = true)
	MaterialBase findByMaterialID(@Param("iMaterialID") int iMaterialID);


	/*
	 * 查询数量
	 */
	@Query(value = "select iNumber  from tMaterialBase where iMaterialID = :iMaterialID", nativeQuery = true)
	int getNumberByMaterialID(@Param("iMaterialID") int iMaterialID);

	/*
	 * 通过id找name
	 */
	@Query(value = "select cMaterialName  from tMaterialBase where iMaterialID = :iMaterialID", nativeQuery = true)
	String findMaterialNameByMaterialID(@Param("iMaterialID") int iMaterialID);

	/*
	 * 通过id修改number
	 */
	@Transactional
	@Modifying
	@Query(value = "update  tMaterialBase set iNumber = :iNumber where iMaterialID = :iMaterialID", nativeQuery = true)
	void updateNumberByMaterialID(@Param("iNumber") int iNumber, @Param("iMaterialID") int iMaterialID);

	/*
	 * 新增id
	 */
	@Transactional
	@Modifying
	@Query(value = "INSERT INTO tMaterialBase(iMaterialID, cMaterialName, cUnit, iNumber, dCreateDate, cCreator, dModifyDate, cReviser, cMemo, cSpare1, cSpare2, cSpare3, cSpare4, cSpare5) VALUES (:materialID, NULL, NULL, 0, NULL, NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL)", nativeQuery = true)
	void save(@Param("materialID") int materialID);

}
